|
|
Introduction
into Microsoft Excel
Microsoft Excel is allows you to create professional spreadsheets and charts.
It performs numerous functions and formulas to assist you in your projects.
This tutorial will help you get started with Microsoft Excel and may solve some
of your problems, but it is a very good idea to use the Help Files that come
with Microsoft Excel.
Starting
Microsoft Excel
As a spreadsheet
application, Microsoft® Excel is used to enter text and numbers to be
organized, calculated and analyzed. Entering formulas allow any changes in the
original numbers to be automatically recalculated throughout an entire
worksheet. Data may be formatted creating an attractive, readable presentation
of tables or charting and text manipulation features produce attractive and
informative reports.
The Worksheet and Cells
Microsoft Excel is a spreadsheet application.
Most of the work performed in Excel is done on a worksheet which is a grid of
rows and columns. The rows are numbered along the left of the grid while the
columns are headed by letters along the top. Each intersection of a row and a
column is called a cell and has a unique reference identifying it. For example,
the cell where column B and row 5 intersect is cell B5. Cells are used to store
data such as labels and numbers, and cell references are used when writing
formulas or referring to cells.
Data is entered into single cells. The cell that has been selected to enter
data is called the active cell and is identified by a heavy border. Only one
cell can be active at a time.
Changing the Active Cell
To change the active cell, move the mouse pointer into a cell and click. For
example, in the preceding illustration if we wanted B4 to be the active cell,
simply click that cell. Upon selecting B4, the formula bar would display the
contents of that cell or 67800. Demonstrated in the illustration above is cell
E4 as the active cell and the formula =C4/D4 displayed in the formula bar since
the contents of cell E4 is the formula dividing the contents of C4 or
$489,000.00 by D4 or $225,000.00.
Scrolling
Through the Worksheet
There are several methods that can be used to scroll through the worksheet:
Entering and Editing Data
Entering Data
As discussed before, data is entered by selecting a cell and entering data. In
the illustration below, if you wanted to enter the year column (column A) you
would click on cell A2, type 1997 and press [ENTER]. Entering the data would
automatically advance the active cell to the next row or cell A3.
The reason A2 was chosen as the first cell to start entering the year labels is
to allow a row (row 1) to be used for headings of each of the columns.
Subsequently, columns B, C, D, etc., were entered. This was done by first
entering the column heading, e.g., Net Income, and then the values 80000,
78900, 67800, etc. Note the values entered were "plain" numbers such
as 80000 instead of the formatted number $80,000.00. It is important to enter
the number as a plain number and format the cell to the required specification.
We will do this in a later section of this tutorial.
Editing Data
Once you have entered data into a cell, that data can be edited by first
clicking on the cell to make the cell active and then clicking on the formula
bar above the worksheet. This allows you to insert or delete characters in that
cell. An alternate method of editing the contents of a cell is to click on the
cell to make it the active cell and then press the [F2] function key at the top
of the keyboard. You can delete the contents of a cell by clicking on the cell
and pressing [Delete] on the keyboard.
Inserting Columns,
Rows and Cells
To
insert a column, click on the column you wish the new column to appear ahead of
or to the left of and click Insert on the menu bar at the top of the screen.
Selecting an existing row to insert a new row ahead of or above is done the
same way. Once you have selected Insert on the menu bar at the top of the
screen, you can click Columns to insert a new column or Rows to insert a new
row. You can experiment inserting new columns, rows and cells. You will notice
that when requesting a new cell, Excel asks if you wish to move the existing
cells to the right or down.
Printing the
Worksheet
Page Setup
To print a worksheet, there are several options which control how the worksheet
looks on the page. Before printing, it is a good idea to review things like the
margins for a page to verify that the worksheet will print as intended. To do
this, from the menu bar at the top of the screen choose File and then Page
Setup... From here, options such as page orientation, paper size, print
margins, and centering printed matter on the page are set.
Print Preview
It is a good idea to always preview before you print so that you can make any
adjustments before printing and save yourself repeated trips to the printer. To
preview what you're about to print, from the menu bar at the top of the screen,
choose File and then Print Preview. You can click the mouse button to magnify
the area around the cursor and click again to return to full page view.
Printing
You might notice that you can switch to Print Preview mode from Page Setup mode
and vise versa. Page Setup and Print Preview modes are available to ease the
task of setting up for printing. Once the worksheet has been prepared for
printing, it can be printed by clicking File from the menu bar at the top of
the screen and then Print...
Saving the Worksheet
You should save your work frequently. If you have a power outage or some other
problem, you can start working again from your last saved version.
When you create a new worksheet and save it for the first time, you are always
asked for a name to assign to the worksheet. From the File option on the menu
bar at the top of the screen, click Save. If the worksheet is new, a dialog box
will appear asking for a name and location to save the worksheet.
If the worksheet you are working on as been previously saved, clicking Save
from the File menu option will save a new copy of the worksheet overwriting the
previous version.
If you would like to save an existing worksheet to another name, thus keeping
the original version in its original condition, from the menu bar at the top of
the screen, click File and then Save As... A dialog box will appear asking for
a name and location to save the worksheet.
Formatting the Worksheet
There are many formatting tools that can be used to highlight a worksheet and
make it more readable. Headings are better defined when they are in bold text
and dollar amounts are better understood when they reflect the standard
currency convention.
In our illustration we have made row 1 the heading
row. To better distinguish it, we have made the contents of each cell bold by
highlighting row 1 and clicking on the Bold Style Button on the toolbar above
the worksheet.
When first entering text into the heading cells, you may notice that the
headings do not appear entirely. That is because the columns are too narrow to
display the full text of the headings. To widen the columns:
Excel adjusts the column widths to fit the cell contents. In the illustration
above, column E (Total Assets Turnover) is the best example of the need to
increase the column width.
Next, when entering number values into cells, it is best to enter the
"plain" number and then format the cell to reflect the appropriate
function of the number. For example, when entering a column of dollar amounts
such as column B (Net Income) in the illustration above, enter the simple
numbers 80000, 78900, 67800, etc. After entering all of the numbers:
Although the illustration above uses the Currency Style, style buttons are
available for percentages, setting commas and decimal places. In addition to
the style buttons on the toolbar, multiple format options are available under
the Format option on the menu bar at the top of the screen.
Working with Formulas
The power of a spreadsheet application is demonstrated most clearly by
formulas. Formulas can be simple, like dividing the values in two cells, or
they can be very complex. The scope of this tutorial will focus on a more
simple example of working with formulas.
Entering and Editing Formulas
A formula can always be identified because it starts with an equal sign (=). To
enter a formula, click on the cell that is to contain the formula and start the
formula by pressing the equal sign (=). This tells Excel to handle the contents
as a formula instead of a label or simple text. In cell E2 (1.724137931) in the
illustration above, we entered the formula =C2/D2. After pressing [ENTER],
Excel automatically advanced to cell E3. Cell E2 displayed the results of the
formula or 1.724137931. The formula =C2/D2 is still the content of the cell,
only Excel has displayed the result of the formula.
In our illustration above, to determine Total Assets Turnover for each year, we
would continue entering formulas in each cell in column E referencing the cells
for Sales divided by Total Assets for each year.
As stated, when a cell contains a formula, the value produced by that formula
is displayed, as in cell E2 in the illustration above. The formula itself,
however, is displayed in the formula bar above the worksheet. Once the formula
is entered in the cell, it will be displayed only if the cell is being edited.
Again, to edit a cell, click on the cell making it the active cell and either
click on the formula bar above the worksheet, or press the [F2] function key at
the top of the keyboard.
Creating
Formulas
Copying Formulas Between Cells
Since our illustration above is relatively short, it
is not much of a problem to type each individual formula. However, if our
illustration required 500 formulas instead of five, it would be very cumbersome
to enter each formula. Once we have entered a formula into cell E2 we can copy
that formula to cells E3 through E6. When we do this, Excel adjusts the
formula's references so that each formula refers to the cells in each respective
row, meaning the formula in row 3 becomes C3/D3, the formula in row 4 becomes
C4/D4, etc. To do this you would:
Cells E3 through E6 will fill with the results of the new formula that was just
copied. Scroll through the cells and notice that the formula in each cell
reflects the appropriate cell references.
Order of
Operations Excel Uses
|
Precedence
|
Operation
|
Operator
|
|
1 |
Exponentiation |
^ |
|
2 |
Multiplication |
* |
|
2 |
Division |
/ |
|
3 |
Addition |
+ |
|
3 |
Subtraction |
- |
|
4 |
Concatenation (putting 2
strings together, like Jenn & ifer) |
& |
|
5 |
Equal To |
= |
|
5 |
Greater Than |
> |
|
5 |
Less Than |
< |
Adding Borders
and Shading to Cells
button
to shade a cell and/or the 
to
give a cell a border
Creating a Chart
Charts are visual representations of worksheet data. Various types of charts
can be created in Excel, such as bar, line and pie charts. Charts can be used
to clarify trends or relationships that might not be apparent in the worksheet
data alone. Once a chart is created, as data on the worksheet is updated the
chart automatically changes to reflect the updates.
To discuss charting data, let's consider the illustration above. We have a
worksheet that contains data cells on the left and those same data cells
charted on the right. To chart the data cell range L9:N14, do the following:
OR
